using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;

namespace PFDB.AppLib
{
    class MySQLSP
    {
        public MySQLSP(string tableName, MySql.Data.MySqlClient.MySqlDataReader row)
        {
            setParams(tableName, row);
        }
        public MySQLSP()
        {
        }
        #region Properties
        const string TABLENAME_C = "{$TableName}";
        const string PROCNAME_C = "{$procName}";
        const string PARAMLIST_C = "{$paramlist}";
        const string CONDITION_C = "{$condition}";
        const string COLUMNLIST_C = "{$columnlist}";
        const string SETLIST_C = "{$setlist}";
        
        const string newline = "\n";
        const string tab = "\t";

        string tableName = String.Empty;
        string condition = String.Empty;
        string paramlist = String.Empty;
        string columnlist = String.Empty;
        string procname = String.Empty;
        string setlist = String.Empty;

        ArrayList Columns = new ArrayList();
        ArrayList ColumnTypes = new ArrayList();
        ArrayList PrimaryKeys = new ArrayList();
        ArrayList AutoIncrement = new ArrayList();


        #endregion Properties

        #region deleteSP

        public string DeleteSP()
        {
            procname = tableName.ToLower() + "_Delete";
            string Statement = String.Empty;
            BuildDeleteSPParameterList();
            Statement = BuildDeleteStatement();
            Statement = BuildWhole(Statement);
            return Statement;
        }
        private string BuildDeleteStatement()
        {
            string Statement = "Delete From " + TABLENAME_C + newline +
                               "Where " + newline +
                                tab + CONDITION_C +";"+ newline;
            BuildDeleteSPCondition();
            Statement=Statement.Replace(TABLENAME_C, tableName);
            Statement=Statement.Replace(CONDITION_C, condition);
            return Statement;
        }
        private bool BuildDeleteSPParameterList()
        {
            paramlist = String.Empty;

            foreach (object key in Columns)
            {
                if (PrimaryKeys.Contains(key))
                {
                    paramlist += ", IN " + (string)key + " " + (string)ColumnTypes[Columns.IndexOf(key)];
                }
            }
            
            if (paramlist != String.Empty)
            {
                paramlist = paramlist.Substring(2);
                return true;
            }
            return false;
        }
        private bool BuildDeleteSPCondition()
        {
            condition = String.Empty;

            foreach (object key in PrimaryKeys)
            {
                condition += "AND " + (string)key + " = p" + (string)key + newline+tab;
            }
            if (condition != String.Empty)
            {
                condition = condition.Substring(4);
                return true;
            }
            return false;
        }

        #endregion deleteSP

        #region insertSP

        public string InsertSP()
        {
            procname = tableName.ToLower() + "_Insert";
            string Statement = String.Empty;
            BuildInsertSPParameterList();
            Statement = BuildInsertStatement();
            Statement = BuildWhole(Statement);
            return Statement;
        }
        private string BuildInsertStatement()
        {

            string Statement = "Insert Into " + TABLENAME_C + newline +
                                tab + "( " + COLUMNLIST_C + " )" + newline +
                                tab + "Values(" + PARAMLIST_C + ");";

            BuildInsertSPColumnList();

            string parlist = ", " + columnlist;
            parlist = parlist.Replace(", ", ", p");
            parlist = parlist.Substring(2);

            Statement=Statement.Replace(TABLENAME_C, tableName);
            Statement=Statement.Replace(COLUMNLIST_C, columnlist);
            Statement=Statement.Replace(PARAMLIST_C, parlist);
            return Statement;
        }
        private void BuildInsertSPColumnList()
        {
            columnlist = String.Empty;
            foreach (object key in Columns)
            {
                if (!AutoIncrement.Contains(key))
                {
                    columnlist += ", " + (string)key;
                }
            }
            columnlist = columnlist.Substring(2);
        }
        private void BuildInsertSPParameterList()
        {
            paramlist = String.Empty;
            foreach (object key in Columns)
            {
                if (!AutoIncrement.Contains(key))
                {
                    paramlist += ", IN p" + (string)key + " " + (string)ColumnTypes[Columns.IndexOf(key)];
                }
            }
            paramlist = paramlist.Substring(2);
        }

        #endregion insertSP

        #region selectSP

        public string SelectSP()
        {
            procname = tableName.ToLower() + "_Select";
            string Statement = String.Empty;
            BuildSelectSPParameterList();
            Statement = BuildSelectStatement();
            Statement = BuildWhole(Statement);
            return Statement;
        }
        private string BuildSelectStatement()
        {
            string Statement = "Select " + COLUMNLIST_C + newline+
                               "From " + TABLENAME_C + newline +
                               "Where " + newline +
                                tab + CONDITION_C+";"+newline;
            BuildSelectSPCondition();
            BuildSelectSpColumnList();
            Statement=Statement.Replace(TABLENAME_C, tableName);
            Statement=Statement.Replace(CONDITION_C, condition);
            Statement=Statement.Replace(COLUMNLIST_C, columnlist);
            return Statement;
        }
        private void BuildSelectSPParameterList()
        {
            paramlist = String.Empty;

            foreach (object key in Columns)
            {
                if (PrimaryKeys.Contains(key))
                {
                    paramlist += ", IN p" + (string)key + " " + (string)ColumnTypes[Columns.IndexOf(key)];
                }
            }
            paramlist = paramlist.Substring(2);
        }
        private void BuildSelectSPCondition()
        {
            foreach (object key in PrimaryKeys)
            {
                condition += " AND " + (string)key + " = p" + (string)key + newline + tab;
            }
            condition = condition.Substring(4);
        }
        private void BuildSelectSpColumnList()
        {
            columnlist = String.Empty;

            foreach (object key in Columns)
            {
                columnlist += ", " + (string)key;
            }
            columnlist = columnlist.Substring(2);
        }

        #endregion selectSP

        #region updateSP

        public string UpdateSP()
        {
            procname = tableName.ToLower() + "_Update";
            string Statement = String.Empty;
            BuildUpdateSPParameterList();
            Statement = BuildUpdateStatement();
            Statement = BuildWhole(Statement);
            return Statement;
        }
        private string BuildUpdateStatement()
        {
            string Statement = "Update " + TABLENAME_C + newline +
                                "SET " + newline+
                                 tab+SETLIST_C+newline+
                                "WHERE " + newline +
                                 tab+CONDITION_C+";";

            BuildUpdateSPSetList();
            BuildUpdateSPCondition();

            Statement=Statement.Replace(TABLENAME_C, tableName);
            Statement=Statement.Replace(SETLIST_C, setlist);
            Statement=Statement.Replace(CONDITION_C, condition);
            return Statement;
        }
        private void BuildUpdateSPParameterList()
        {
            paramlist = String.Empty;

            foreach (object key in Columns)
            {
                paramlist += ", IN p" + (string)key + " " + (string)ColumnTypes[Columns.IndexOf(key)];
            }
            paramlist = paramlist.Substring(2);
        }
        private bool BuildUpdateSPCondition()
        {
            condition = String.Empty;

            foreach (object key in PrimaryKeys)
            {
                condition += "AND " + (string)key + " = p" + (string) key + newline+tab;
            }
            if (condition != String.Empty)
            {
                condition = condition.Substring(4);
                return true;
            }
            return false;
        }
        private void BuildUpdateSPSetList()
        {
            setlist = String.Empty;

            foreach (object key in Columns)
            {
                if (!AutoIncrement.Contains(key))
                {
                    setlist += ", " + (string)key + " = p" + (string)key+newline+tab;
                }
            }
            if (setlist != String.Empty)
            {
                setlist = setlist.Substring(2);
            }
        }

        #endregion udateSP

		#region selectAllSP
		public string SelectAllSP()
		{
			procname = tableName.ToLower() + "_SelectAll";
			string Statement = String.Empty;
			BuildSelectAllSPParameterList();
			Statement = BuildSelectAllStatement();
			Statement = BuildWhole(Statement);
			return Statement;
		}
		private string BuildSelectAllStatement()
		{
			string Statement = "Select " + COLUMNLIST_C + newline +
							   "From " + TABLENAME_C + newline +";"+
							    newline;
			BuildSelectAllSpColumnList();
			Statement = Statement.Replace(TABLENAME_C, tableName);
			Statement = Statement.Replace(COLUMNLIST_C, columnlist);
			return Statement;
		}
		private void BuildSelectAllSPParameterList()
		{
			paramlist = String.Empty;
		}
		private void BuildSelectAllSpColumnList()
		{
			columnlist = String.Empty;

			foreach (object key in Columns)
			{
				columnlist += ", " + (string)key;
			}
			columnlist = columnlist.Substring(2);
		}
		#endregion selectAllSP

		#region GenMethods

		private string BuildWhole(string innerStatement)
        {

            string PARAMLIST_C = "{$paramList}";
            string Statement = "DROP PROCEDURE IF EXISTS " + PROCNAME_C + ";" + newline+
                              "CREATE PROCEDURE `" + PROCNAME_C + "` ( " + PARAMLIST_C + " ) " + newline +
                               tab+"NOT DETERMINISTIC"+newline+
                               tab+"CONTAINS SQL"+newline+
                               tab+"SQL SECURITY INVOKER"+newline+
                               tab+"COMMENT \'\'"+newline+
                               "BEGIN" + newline+
                                tab+innerStatement.Replace("\n","\n\t") + newline+//Add an extra tab
                               "END;" + newline;

            Statement=Statement.Replace(PROCNAME_C, procname);
            Statement=Statement.Replace(PARAMLIST_C, paramlist);
            return Statement;
        }
        public void setParams(string tableName, MySql.Data.MySqlClient.MySqlDataReader row)
        {
            this.Clear();
            this.tableName = tableName;
            while (row.Read())
            {
                //if column is auto_increment
                if (row["Extra"] != null && (string)row["Extra"] == "auto_increment")
                {
                    AutoIncrement.Add((string)row["Field"]);
                }

                //if column is primary key
                if (row["Key"] != null && (string)row["Key"] == "PRI")
                {
                    PrimaryKeys.Add(row["Field"]);
                }

                Columns.Add((string)row["Field"]);
                ColumnTypes.Add((string)row["Type"]);
            }
            row.Close();
        }
        public void Clear()
        {
            tableName = String.Empty;
            condition = String.Empty;
            paramlist = String.Empty;
            columnlist = String.Empty;
            procname = String.Empty;
            setlist = String.Empty;

            Columns.Clear();
            ColumnTypes.Clear();
            PrimaryKeys.Clear();
            AutoIncrement.Clear();
        }
        public string ParamList
        {
            get 
            { 
                return paramlist; 
            }
            set
            {
                paramlist = value;
            }
        }
        public string Procname
        {
            get
            {
                return procname;
            }
            set
            {
                procname = value;
            }
        }
        #endregion GenMethods
    }
}
